package jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

public class BatchDemo {
	public static void main(String[] args) throws DocumentException, Exception {
//		backupTest();
		importBackup();
	}

	static void triggerTest(){
		
	}
	
	private static void importBackup() throws SQLException, DocumentException, ClassNotFoundException, Exception {
		File f = new File("/home/tarena/0hean/hean/src/jdbc/backupstudent.xml");
		String sql="insert into hh_stu (id,name,age,birthday) values(?,?,?,?)";
		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs = null;
		try{
			con=DBUtil.open();
			pst=con.prepareStatement(sql);
			
			SAXReader reader = new SAXReader();
			InputStreamReader in = new InputStreamReader(new FileInputStream(f),"utf-8");
			Document doc = reader.read(in);
			in.close();
			
			List<Element> list = doc.getRootElement().elements("student");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
			int n=1;
			for (Element e : list) {
				int id = Integer.parseInt(e.element("id").getText());
				String name = e.element("name").getText();
				String age = e.element("age").getText();
				String birthday = e.element("birthday").getText();
				java.sql.Date d = null;
				if(!"null".equals(birthday)){
					Date date = sdf.parse(birthday);
					d = new java.sql.Date(date.getTime());
				}
				pst.setInt(1, id);
				name= "null".equals(name)?null:name;
				pst.setString(2, name);
				if("null".equals(age)){
					pst.setNull(3, Types.NUMERIC);
				}else{
					pst.setInt(3, Integer.parseInt(age));
				}
				pst.setDate(4, d);
				pst.addBatch();
				if(n++%10==0){
					pst.executeBatch();
				}
			}
			int[] arr = pst.executeBatch();
		}finally{
			DBUtil.close(con, pst, rs);
		}
	}

	private static void backupTest() throws Exception {
		File f = new File("/home/tarena/0hean/hean/src/jdbc/backupstudent.xml");
		backup(f);
	}

	private static void backup(File file) throws Exception {
		String sql="select * from hh_stu";
		Connection con=null;
		Statement stm=null;
		ResultSet rs = null;
		try{
			con=DBUtil.open();
			stm=con.createStatement();
			rs=stm.executeQuery(sql);
			Document doc = DocumentHelper.createDocument();
			Element students = doc.addElement("students");
			while (rs.next()) {
				Element student = students.addElement("student");
				student.addElement("id").addText(rs.getString("id"));
//				name为空则输出null,或者null==rs.getString("name")?"":rs.getString("name");名字为空输出<name></name>
				student.addElement("name").addText(rs.getString("name")+"");
				student.addElement("age").addText(rs.getString("age")+"");
				student.addElement("birthday").addText(rs.getString("birthday")+"");
			}
			OutputStreamWriter out= new OutputStreamWriter(new FileOutputStream(file),"utf-8");
			OutputFormat f = OutputFormat.createPrettyPrint();
			XMLWriter writer = new XMLWriter(out,f);
			writer.write(doc);
			out.close();
		}finally{
			DBUtil.close(con, stm, rs);
		}
	}
}
